package com.hellogood.yaohuo.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.shared.vo.BasicVo;

public class CommonDAO {

	Logger log = Logger.getLogger(this.getClass());

	// 配送退货
	// ----配送退货取的进价为表头门店组织的jprice （而非C001，传进去的组织变量是表头门店非C001）
	public BasicVo getBasicVoPeisongTuihuo(String code, String orgCode) {
		BasicVo vo = null;
		String hxDbName = DBConnection.hxDbName;
		String sql = "IF EXISTS(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE plustatus<>'9' and (a.plucode='" + code + "' or a.barcode='"
				+ code + "'  )) begin SELECT a.plucode,a.barcode,a.pluname,a.depcode,(SELECT depName FROM " + hxDbName
				+ "..dept WHERE DepCode = a.depcode) AS depName, a.clscode,(SELECT clsName FROM " + hxDbName
				+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,(SELECT g.ojprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='"
				+ orgCode + "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE  a.barcode='" + code + "' or a.plucode='" + code
				+ "')) AS psPrice,(SELECT g.jprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE  a.barcode='" + code + "' or a.plucode='" + code
				+ "')) AS jprice ,(SELECT g.price FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' AND g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE a.barcode='" + code + "' or a.plucode='" + code
				+ "')) as price FROM " + hxDbName + "..goods a WHERE a.plucode='" + code
				+ "' end else  begin SELECT a.plucode,(SELECT b.barcode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code
				+ "') as barcode ,a.pluname,a.depcode, (SELECT depName FROM " + hxDbName
				+ "..dept WHERE DepCode = a.depcode) AS depName,a.clscode,(SELECT clsName FROM " + hxDbName
				+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,(SELECT g.jprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='"
				+ orgCode + "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE a.barcode='" + code + "' OR a.plucode='" + code
				+ "')) AS psPrice,(SELECT g.jprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' and g.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code
				+ "')) as jprice ,(SELECT g.price FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' and  g.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code + "')) as price FROM " + hxDbName
				+ "..goods a WHERE a.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code + "') end";
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				vo = new BasicVo();
				vo.setBarCode(rs.getString("barCode") == null ? "" : rs.getString("barCode").trim());
				vo.setDepCode(rs.getString("DepCode") == null ? "" : rs.getString("DepCode").trim());
				vo.setDepName(rs.getString("depName") == null ? "" : rs.getString("depName").trim());
				vo.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				vo.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				vo.setUnit(rs.getString("Unit") == null ? "" : rs.getString("Unit").trim());
				vo.setjPrice(rs.getBigDecimal("jPrice"));
				vo.setPsPrice(rs.getBigDecimal("psPrice"));
				vo.setPrice(rs.getBigDecimal("price"));
				vo.setClsCode(rs.getString("clsCode") == null ? "" : rs.getString("clsCode").trim());
				vo.setClsName(rs.getString("clsName") == null ? "" : rs.getString("clsName").trim());
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return vo;
	}

	// 配送
	// -----配送价格取为ojprice(软件界面的配送价取这个)-----配送价格取为ojprice(软件界面的配送价取这个)
	public BasicVo getBasicVoPeisong(String code, String orgCode) {
		BasicVo vo = null;
		String hxDbName = DBConnection.hxDbName;
		String sql_temp = "SELECT OrgType FROM " + hxDbName + "..SubShop WHERE OrgCode = '" + orgCode + "'";
		String sql = "IF EXISTS(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE plustatus<>'9' and (a.plucode='" + code + "' or a.barcode='"
				+ code + "'  )) begin SELECT a.plucode,a.barcode,a.pluname,a.depcode,(SELECT depName FROM " + hxDbName
				+ "..dept WHERE DepCode = a.depcode) AS depName, a.clscode,(SELECT clsName FROM " + hxDbName
				+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,(SELECT g.ojprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='"
				+ orgCode + "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE  a.barcode='" + code + "' or a.plucode='" + code
				+ "')) AS psPrice, (SELECT g.jprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE  a.barcode='" + code + "' or a.plucode='" + code
				+ "')) AS jprice ,(SELECT g.price FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' AND g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE a.barcode='" + code + "' or a.plucode='" + code
				+ "')) as price FROM " + hxDbName + "..goods a WHERE a.plucode='" + code
				+ "' end else  begin SELECT a.plucode,(SELECT b.barcode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code
				+ "') as barcode ,a.pluname,a.depcode, (SELECT depName FROM " + hxDbName
				+ "..dept WHERE DepCode = a.depcode) AS depName,a.clscode,(SELECT clsName FROM " + hxDbName
				+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,(SELECT g.ojprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='"
				+ orgCode + "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE a.barcode='" + code + "' OR a.plucode='" + code
				+ "')) AS psPrice,(SELECT g.jprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' and g.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code
				+ "')) as jprice ,(SELECT g.price FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' and  g.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code + "')) as price FROM " + hxDbName
				+ "..goods a WHERE a.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code + "') end";
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Statement stmt = null;
		ResultSet rs_temp = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			stmt = conn.createStatement();
			rs_temp = stmt.executeQuery(sql_temp);
			String orgType = "0";
			while (rs_temp.next()) {
				orgType = rs_temp.getString("OrgType") == null ? "0" : rs_temp.getString("OrgType").trim();
			}
			if (orgType.equals("1")) {
				sql = "IF EXISTS(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE plustatus<>'9' and (a.plucode='" + code + "' or a.barcode='"
						+ code + "'  )) begin SELECT a.plucode,a.barcode,a.pluname,a.depcode,(SELECT depName FROM " + hxDbName
						+ "..dept WHERE DepCode = a.depcode) AS depName, a.clscode,(SELECT clsName FROM " + hxDbName
						+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,(SELECT g.PsPrice FROM " + hxDbName
						+ "..goods g WHERE g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE  a.barcode='" + code + "' or a.plucode='"
						+ code + "')) AS psPrice, (SELECT g.jprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
						+ "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE  a.barcode='" + code + "' or a.plucode='" + code
						+ "')) AS jprice ,(SELECT g.price FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
						+ "' AND g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE a.barcode='" + code + "' or a.plucode='" + code
						+ "')) as price FROM " + hxDbName + "..goods a WHERE a.plucode='" + code
						+ "' end else  begin SELECT a.plucode,(SELECT b.barcode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code
						+ "') as barcode ,a.pluname,a.depcode, (SELECT depName FROM " + hxDbName
						+ "..dept WHERE DepCode = a.depcode) AS depName,a.clscode,(SELECT clsName FROM " + hxDbName
						+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,(SELECT g.PsPrice FROM " + hxDbName
						+ "..goods g WHERE g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE a.barcode='" + code + "' OR a.plucode='"
						+ code + "')) AS psPrice,(SELECT g.jprice FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
						+ "' and g.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code
						+ "')) as jprice ,(SELECT g.price FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
						+ "' and  g.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code + "')) as price FROM "
						+ hxDbName + "..goods a WHERE a.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code
						+ "') end";
			}
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				vo = new BasicVo();
				vo.setBarCode(rs.getString("barCode") == null ? "" : rs.getString("barCode").trim());
				vo.setDepCode(rs.getString("DepCode") == null ? "" : rs.getString("DepCode").trim());
				vo.setDepName(rs.getString("depName") == null ? "" : rs.getString("depName").trim());
				vo.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				vo.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				vo.setUnit(rs.getString("Unit") == null ? "" : rs.getString("Unit").trim());
				vo.setjPrice(rs.getBigDecimal("jPrice"));
				vo.setPsPrice(rs.getBigDecimal("psPrice"));
				vo.setPrice(rs.getBigDecimal("price"));
				vo.setClsCode(rs.getString("clsCode") == null ? "" : rs.getString("clsCode").trim());
				vo.setClsName(rs.getString("clsName") == null ? "" : rs.getString("clsName").trim());
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs_temp != null) {
				try {
					rs_temp.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return vo;
	}

	// 普通
	public BasicVo getBasicVo(String code, String orgCode) {
		BasicVo vo = null;
		String hxDbName = DBConnection.hxDbName;
		String sql = "if  exists(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE plustatus<>'9' and (a.plucode='" + code + "' or a.barcode='"
				+ code + "'  )) begin SELECT a.plucode,a.barcode,a.pluname,a.depcode,(SELECT depName FROM " + hxDbName
				+ "..dept WHERE DepCode = a.depcode) AS depName, a.clscode,(SELECT clsName FROM " + hxDbName
				+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,a.psPrice,(SELECT g.ojprice FROM " + hxDbName
				+ "..goodsorg g WHERE g.orgcode='" + orgCode + "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE  a.barcode='"
				+ code + "' or a.plucode='" + code + "')) as jprice , (SELECT g.price FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='" + orgCode
				+ "' and g.plucode=(SELECT a.plucode FROM " + hxDbName + "..goods a WHERE  a.barcode='" + code + "' or a.plucode='" + code
				+ "')) as price FROM " + hxDbName + "..goods a WHERE  a.plucode='" + code
				+ "' end else  begin SELECT a.plucode,(SELECT b.barcode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code
				+ "') as barcode,a.pluname,a.depcode,(SELECT depName FROM " + hxDbName
				+ "..dept WHERE DepCode = a.depcode) AS depName,a.clscode,(SELECT clsName FROM " + hxDbName
				+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,a.psPrice, (SELECT g.ojprice FROM " + hxDbName
				+ "..goodsorg g WHERE g.orgcode='" + orgCode + "' and g.plucode=(SELECT b.plucode FROM " + hxDbName
				+ "..barcoderef b WHERE b.barcode='" + code + "')) as jprice,(SELECT g.price FROM " + hxDbName + "..goodsorg g WHERE g.orgcode='"
				+ orgCode + "' and  g.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code + "')) as price  FROM "
				+ hxDbName + "..goods a WHERE a.plucode=(SELECT b.plucode FROM " + hxDbName + "..barcoderef b WHERE b.barcode='" + code + "') end";
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				vo = new BasicVo();
				vo.setBarCode(rs.getString("barCode") == null ? "" : rs.getString("barCode").trim());
				vo.setDepCode(rs.getString("DepCode") == null ? "" : rs.getString("DepCode").trim());
				vo.setDepName(rs.getString("depName") == null ? "" : rs.getString("depName").trim());
				vo.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				vo.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				vo.setUnit(rs.getString("Unit") == null ? "" : rs.getString("Unit").trim());
				vo.setjPrice(rs.getBigDecimal("jPrice"));
				vo.setPsPrice(rs.getBigDecimal("psPrice"));
				vo.setPrice(rs.getBigDecimal("price"));
				vo.setClsCode(rs.getString("clsCode") == null ? "" : rs.getString("clsCode").trim());
				vo.setClsName(rs.getString("clsName") == null ? "" : rs.getString("clsName").trim());
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return vo;
	}

	// 检查版本号
	public boolean checkVersion() {
		String sql = "SELECT * FROM VERSION WHERE version = '1.0603'";
		boolean yes = false;
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				yes = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return yes;
	}
}
